CREATE TABLE [dbo].[tb_CierresDeReestructura]
(
[idCierreReestructura] [int] NOT NULL IDENTITY(1, 1),
[Reestructura] [int] NOT NULL,
[PlazaACerrar] [int] NOT NULL,
[Observaciones] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Estado] [int] NOT NULL CONSTRAINT [DF_tb_CierresDeReestructura_Estado] DEFAULT ((12))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[delCPlaza] ON [dbo].[tb_CierresDeReestructura] 
FOR DELETE 
AS

DECLARE @Estado INT
DECLARE @Reestructura INT

SELECT @Estado = R.Estado, @Reestructura = R.IdReestructura FROM deleted INNER JOIN tb_Reestructuras R ON R.IdReestructura = Reestructura

IF @Estado NOT IN(15, 16, 18) BEGIN
	UPDATE tb_Reestructuras SET Estado = 12 WHERE IdReestructura = @Reestructura
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[insCPlaza] ON [dbo].[tb_CierresDeReestructura] 
FOR INSERT 
AS

DECLARE @Estado INT
DECLARE @Reestructura INT

SELECT @Estado = R.Estado, @Reestructura = R.IdReestructura FROM inserted INNER JOIN tb_Reestructuras R ON R.IdReestructura = Reestructura

IF @Estado=18 BEGIN
	ROLLBACK transaction 
	RAISERROR('La Reestructura ya esta en vigencia (fue ejecutada), imposible agregar mas plazas', 16,1)
END

IF @Estado NOT IN(15, 16, 18) BEGIN
	UPDATE tb_Reestructuras SET Estado = 12 WHERE IdReestructura = @Reestructura
END
GO
ALTER TABLE [dbo].[tb_CierresDeReestructura] ADD CONSTRAINT [PK_tb_CierresDeReestructura] PRIMARY KEY NONCLUSTERED  ([idCierreReestructura]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_CierresDeReestructura] WITH NOCHECK ADD CONSTRAINT [FK_tb_CierresDeReestructura_tb_EstadosDeReestructura] FOREIGN KEY ([Estado]) REFERENCES [dbo].[tb_EstadosDeReestructura] ([idEstadoReestructura])
GO
ALTER TABLE [dbo].[tb_CierresDeReestructura] WITH NOCHECK ADD CONSTRAINT [FK_tb_CierresDeReestructura_tb_Plazas] FOREIGN KEY ([PlazaACerrar]) REFERENCES [dbo].[tb_Plazas] ([idPlaza])
GO
ALTER TABLE [dbo].[tb_CierresDeReestructura] WITH NOCHECK ADD CONSTRAINT [FK_tb_CierresDeReestructura_tb_Reestructuras] FOREIGN KEY ([Reestructura]) REFERENCES [dbo].[tb_Reestructuras] ([IDReestructura])
GO
ALTER TABLE [dbo].[tb_CierresDeReestructura] NOCHECK CONSTRAINT [FK_tb_CierresDeReestructura_tb_Plazas]
GO
